#include <stdlib.h>
#include <string.h>
#include "sqlite3.h"
#include "log.h"
#include "sql_func.h"

/*全局变量区*/


/*静态变量区*/
static sqlite3 *db_handle = NULL;

int InitDatabase(const char *db_name)
{
    // 新建数据库
	int ret = sqlite3_open( db_name,&db_handle );
	if ( ret != SQLITE_OK )
	{
		log_error("Error: Can not open database [%s]:%s", db_name, sqlite3_errmsg(db_handle) );
	}
    else
    {
		log_info("open %s!", db_name);
    }

    return ret;
}

void DeinitDatabase(void)
{
    // 关闭数据库连接
    sqlite3_close(db_handle);
}

/**********通用回调函数************/
/*查询的回调函数*/
int SqlCallbackRowCount(void* data, int argc, char** argv, char** azColName)
{
    int* rowCount = (int*)data;

    // 将查询结果转换为整数
    *rowCount = atoi(argv[0]);

    return 0;
}

int SqlCallbackString(void* data, int argc, char** argv, char** azColName)
{
    char* result = (char*)data;

    memcpy(result, argv[0], strlen(argv[0]));

    return 0;
}

/**************fast_conble_gateway_list***************/
int SqlInsertGatewayList(FastConbleGatewayList* gateway)
{
    char sql[1024];
    sqlite3_snprintf(1024, sql, "INSERT INTO fast_conble_gateway_list (ip, mac) VALUES ('%s', '%s')",
                     gateway->ip, gateway->mac);

    // 执行插入操作
    int rc = sqlite3_exec(db_handle, sql, 0, 0, 0);
    if (rc != SQLITE_OK) {
        log_error("SQL error: %s", sqlite3_errmsg(db_handle));
    }
    log_debug("%s", sql);
    return rc;
}

int SqlQueryGatawayMacCount(int (*callback)(void*,int,char**,char**), int *rowCount)
{
    // 准备SQL语句
    char sql[512];
    sqlite3_snprintf(512, sql, "SELECT count(mac) FROM fast_conble_gateway_list");

    // 执行SQL语句
    int rc = sqlite3_exec(db_handle, sql, callback, (void *)rowCount, 0);
    if (rc != SQLITE_OK) {
        log_error("SQL error: %s", sqlite3_errmsg(db_handle));
    }
    log_debug("%s", sql);
    return rc;
}

int SqlQueryGatawayMac(int (*callback)(void*,int,char**,char**), FastConbleGatewayList *list)
{
    // 准备SQL语句
    char sql[512];
    sqlite3_snprintf(512, sql, "SELECT * FROM fast_conble_gateway_list");

    // 执行SQL语句
    int rc = sqlite3_exec(db_handle, sql, callback, (void *)list, 0);
    if (rc != SQLITE_OK) {
        log_error("SQL error: %s", sqlite3_errmsg(db_handle));
    }
    log_debug("%s", sql);

    return rc;
}

int SqlCallbackGatewayList(void* data, int argc, char** argv, char** azColName)
{
    log_debug("%s called", __func__);
    FastConbleGatewayList* result = (FastConbleGatewayList*)data;

    // 分配内存并复制查询结果
    result->ip = strdup(argv[0]);
    log_debug("%s", strdup(argv[100]));
    result->mac = strdup(argv[1]);
    log_debug("%s", strdup(argv[1]));
    log_debug("%s end", __func__);
    return 0;
}

/***************fast_conble_data_info********************/
int SqlInsertDataInfo(FastConbleDataInfo* dataInfo)
{
    if (dataInfo == NULL || dataInfo->mtag == NULL) {
        log_error("Error: Invalid input parameters dataInfo[%p] dataInfo->mtag[%p]", 
                (void*)dataInfo, (void*)dataInfo->mtag);
        return SQLITE_ERROR;
    }

    // 准备SQL语句
    char sql[1024] = "";

    // 查询记录是否存在
    int rowCount = 0;
    sqlite3_snprintf(1024, sql, "SELECT count(mtag) FROM fast_conble_data_info where mtag='%s'", dataInfo->mtag);
    int rc = sqlite3_exec(db_handle, sql, SqlCallbackRowCount, (void *)&rowCount, 0);
    if (rc != SQLITE_OK)
    {
        log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        return rc;
    }
    log_debug("%s", sql);

    if (rowCount == 0)  //不存在记录，则插入语句
    {
        sqlite3_snprintf(1024, sql, "INSERT INTO fast_conble_data_info (mtag, validTime, updateTime, devqueryTime, familyId) VALUES ('%s', %d, '%s', '%s', '%s')",
                     dataInfo->mtag, dataInfo->validTime, dataInfo->updateTime, dataInfo->devqueryTime, dataInfo->familyId);

        // 执行SQL语句
        rc = sqlite3_exec(db_handle, sql, 0, 0, 0);
        if (rc != SQLITE_OK)
        {
            log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        }
    } 
    else //存在记录则更新表信息
    {
        if (strcmp(dataInfo->mtag, "familyInfo") == 0)  //如果是familyInfo则增加一个familyId
        {
            sqlite3_snprintf(1024, sql, "UPDATE fast_conble_data_info SET validTime = %d, updateTime = '%s', \
                     devqueryTime = '%s', familyId = '%s' WHERE mtag = '%s'",
                     dataInfo->validTime, dataInfo->updateTime, dataInfo->devqueryTime, dataInfo->familyId, dataInfo->mtag);
        }
        else
        {
            sqlite3_snprintf(1024, sql, "UPDATE fast_conble_data_info SET validTime = %d, updateTime = '%s', devqueryTime = '%s' WHERE mtag = '%s'",
                     dataInfo->validTime, dataInfo->updateTime, dataInfo->devqueryTime, dataInfo->mtag);
        }

        // 执行SQL语句
        rc = sqlite3_exec(db_handle, sql, 0, 0, 0);
        if (rc != SQLITE_OK)
        {
            log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        }
    }
    log_debug("%s", sql);

    return rc;
}

// 函数用于执行查询操作
int SqlQueryDataInfoByMtag(const char* mtag, FastConbleDataInfo* resultDataInfo)
{
    if (mtag == NULL || resultDataInfo == NULL)
    {
        log_error("Invalid input parameters mtag[%p] resultDataInfo[%p]", mtag, resultDataInfo);
        return SQLITE_ERROR;
    }

    char sql[512];
    sqlite3_snprintf(512, sql, "SELECT * FROM fast_conble_data_info WHERE mtag = '%s' LIMIT 1", mtag);

    // 执行查询操作
    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db_handle, sql, -1, &stmt, 0);
    if (rc != SQLITE_OK) {
        log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        return rc;
    }

    // 执行查询
    rc = sqlite3_step(stmt);
    if (rc == SQLITE_ROW) {
        // 获取查询结果
        resultDataInfo->mtag = strdup(sqlite3_column_text(stmt, 0));
        resultDataInfo->validTime = sqlite3_column_int(stmt, 1);
        resultDataInfo->updateTime = strdup(sqlite3_column_text(stmt, 2));
        resultDataInfo->devqueryTime = strdup(sqlite3_column_text(stmt, 3));
        resultDataInfo->familyId = strdup(sqlite3_column_text(stmt, 4));
    }

    // 释放资源
    sqlite3_finalize(stmt);

    return 0;
}

/***************fast_conble_devlist********************/
int SqlInsertDevlist(FastConbleDevlist* devlist)
{
    if (devlist == NULL) {
        log_error("Error: Invalid input parameters devlist[%p]", (void*)devlist);
        return SQLITE_ERROR;
    }

    // 准备SQL语句
    char sql[1024];

    // 查询记录是否存在
    int rowCount = 0;
    sqlite3_snprintf(1024, sql, "SELECT count(did) FROM fast_conble_devlist");
    int rc = sqlite3_exec(db_handle, sql, SqlCallbackRowCount, (void *)&rowCount, 0);
    if (rc != SQLITE_OK)
    {
        log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        return rc;
    }
    log_debug("%s", sql);

    
    if (rowCount == 0)  //不存在记录，则插入语句
    {
        sqlite3_snprintf(1024, sql, "INSERT INTO fast_conble_devlist "
                        "(did, pid, devname, room, roomId, devicetypeflag, "
                        "devlist, version, extendInfo, terminalid, aeskey, "
                        "shortaddr, bletoken, sdid) VALUES "
                        "('%s', '%s', '%s', '%s', %d, %d, %d, '%s', '%s', %d, '%s', '%s', '%s', '%s')",
                     devlist->did, devlist->pid, devlist->devname, devlist->room, devlist->roomId, 
                     devlist->devicetypeflag, devlist->ignoreflag, devlist->version, devlist->extendInfo, 
                     devlist->terminalid, devlist->aeskey, devlist->shortaddr, devlist->bletoken, devlist->sdid);

        int rc = sqlite3_exec(db_handle, sql, 0, 0, 0);
        if (rc != SQLITE_OK) {
            log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        }
    }
    else //存在记录则更新表信息
    {
        sqlite3_snprintf(1024, sql, "UPDATE fast_conble_devlist SET "
                                "devname = '%s', "
                                "room = '%s', "
                                "roomId = %d, "
                                "devicetypeflag = %d, "
                                "ignoreflag = %d, "
                                "version = '%s', "
                                "extendInfo = '%s', "
                                "terminalid = %d, "
                                "aeskey = '%s', "
                                "bletoken = '%s', "
                                "sdid = '%s' "
                                "WHERE pid = '%s' AND did = '%s' AND shortaddr = '%s'",
                     devlist->devname, devlist->room, devlist->roomId, devlist->devicetypeflag,
                     devlist->ignoreflag, devlist->version, devlist->extendInfo, devlist->terminalid,
                     devlist->aeskey, devlist->bletoken, devlist->sdid, devlist->pid, devlist->did, devlist->shortaddr);

        rc = sqlite3_exec(db_handle, sql, 0, 0, 0);
        if (rc != SQLITE_OK)
        {
            log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        }
    }
    log_debug("%s", sql);

    return rc;
}

int SqlDeleteDevlist(FastConbleDevlist* devlist)
{

}

/************fast_conble_family_info_roomlist***************/
int SqlInsertFamilyInfoRoomlist(FastConbleFamilyInfoRoomlist* familyInfo)
{
    if (familyInfo == NULL || familyInfo->roomName == NULL || familyInfo->roomId == -1) {
        log_error("Error: Invalid input parameters familyInfo[%p] roomName[%p] roomId[%d].",
                  (void*)familyInfo, (void*)familyInfo->roomName, familyInfo->roomId);
        return SQLITE_ERROR;
    }

    // 准备SQL语句
    char sql[512] = "";

    // 查询记录是否存在
    int rowCount = 0;
    sqlite3_snprintf(512, sql, "SELECT count(roomId) FROM fast_conble_family_info_roomlist "
                    "where roomId = %d", familyInfo->roomId);
    int rc = sqlite3_exec(db_handle, sql, SqlCallbackRowCount, (void *)&rowCount, 0);
    if (rc != SQLITE_OK)
    {
        log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        return rc;
    }
    log_debug("%s", sql);

    if (rowCount == 0)  //不存在记录，则插入语句
    {
        sqlite3_snprintf(512, sql, "INSERT INTO fast_conble_family_info_roomlist "
                        "(roomId, roomName) VALUES (%d, '%s')",
                        familyInfo->roomId, familyInfo->roomName);

        // 执行SQL语句
        int rc = sqlite3_exec(db_handle, sql, 0, 0, 0);
        if (rc != SQLITE_OK)
        {
            log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        }
    }
    else
    {
        sqlite3_snprintf(512, sql, "UPDATE fast_conble_family_info_roomlist SET "
                                "roomName = '%s' "
                                "WHERE roomId = %d",
                     familyInfo->roomName, familyInfo->roomId);

        // 执行更新操作
        int rc = sqlite3_exec(db_handle, sql, 0, 0, 0);
        if (rc != SQLITE_OK)
        {
            log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        }
    }
    log_debug("%s", sql);

    return rc;
}

/************fast_conble_grouplist***************/
int SqlInsertGroupList(FastConbleGroupList* group)
{
    if (group == NULL || group->groupId == -1)
    {
        log_error("Error: Invalid input parameters group[%p] group->groupId[%d]", 
                (void*)group, group->groupId);
        return SQLITE_ERROR;
    }

    // 准备SQL语句
    char sql[1024] = "";

    // 查询记录是否存在
    int rowCount = 0;
    sqlite3_snprintf(1024, sql, "SELECT count(groupId) FROM fast_conble_grouplist where groupId=%d", group->groupId);
    int rc = sqlite3_exec(db_handle, sql, SqlCallbackRowCount, (void *)&rowCount, 0);
    if (rc != SQLITE_OK)
    {
        log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        return rc;
    }
    log_debug("%s", sql);

    if (rowCount == 0)  //不存在记录，则插入语句
    {
        // 准备SQL语句
        sqlite3_snprintf(1024, sql, "INSERT INTO fast_conble_grouplist "
                        "(groupId, groupName, roomId, roomName, blecatetory, ignoreflag) "
                        "VALUES (%d, '%s', %d, '%s', '%s', %d)",
                        group->groupId, group->groupName, group->roomId, group->roomName, 
                        group->blecatetory, group->ignoreflag);
        // 执行SQL语句
        int rc = sqlite3_exec(db_handle, sql, 0, 0, 0);
        if (rc != SQLITE_OK) {
            log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        }
    }
    else
    {
        sqlite3_snprintf(2048, sql, "UPDATE fast_conble_grouplist SET "
                                "groupName = '%s', "
                                "roomId = %d, "
                                "roomName = '%s', "
                                "blecatetory = '%s', "
                                "ignoreflag = %d "
                                "WHERE groupId = %d",
                     group->groupName, group->roomId, group->roomName, group->blecatetory,
                     group->ignoreflag, group->groupId);
        if (rc != SQLITE_OK) {
            log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        }
    }
    log_debug("%s", sql);

    return rc;
}

/************fast_conble_scenelist***************/
int SqlInsertSceneList(FastConbleSceneList* scene)
{
    if (scene == NULL || scene->name == NULL || scene->room == NULL ||
        scene->image == NULL || scene->bletoken == NULL || scene->moduleid == NULL) {
        log_error("Error: Invalid input parameters sceneList[%p] name[%p] \
            room[%p] image[%p] bletoken[%p] moduleid[%p].",
                  (void*)scene, (void*)scene->name, (void*)scene->room,
                  (void*)scene->image, (void*)scene->bletoken, (void*)scene->moduleid);
        return SQLITE_ERROR;
    }

    // 准备SQL语句
    char sql[1024] = "";
    int rowCount = 0;

    // 查询记录是否存在
    sqlite3_snprintf(1024, sql, "SELECT count(moduleid) FROM fast_conble_scenelist where moduleid='%s'", scene->moduleid);
    int rc = sqlite3_exec(db_handle, sql, SqlCallbackRowCount, (void *)&rowCount, 0);
    if (rc != SQLITE_OK)
    {
        log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        return rc;
    }
    log_debug("%s", sql);

    if (rowCount == 0)  //不存在记录，则插入语句
    {
        sqlite3_snprintf(1024, sql, "INSERT INTO fast_conble_scenelist "
                        "(name, room, roomId, image, backColor, bletoken, moduleid, ignoreflag) "
                        "VALUES ('%s', '%s', %d, '%s', %d, '%s', '%s', %d)",
                        scene->name, scene->room, scene->roomId, scene->image, scene->backColor, 
                        scene->bletoken, scene->moduleid, scene->ignoreflag);

        // 执行SQL语句
        int rc = sqlite3_exec(db_handle, sql, 0, 0, 0);
        if (rc != SQLITE_OK)
        {
            log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        }
    }
    else
    {
        sqlite3_snprintf(2048, sql, "UPDATE fast_conble_scenelist SET "
                                "name = '%s', "
                                "room = '%s', "
                                "roomId = %d, "
                                "image = '%s', "
                                "backColor = %d, "
                                "bletoken = '%s', "
                                "ignoreflag = %d "
                                "WHERE moduleid = '%s'",
                     scene->name, scene->room, scene->roomId, scene->image,
                     scene->backColor, scene->bletoken, scene->ignoreflag, scene->moduleid);

        // 执行更新操作
        int rc = sqlite3_exec(db_handle, sql, 0, 0, 0);
        if (rc != SQLITE_OK)
        {
            log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        }
    }
    log_debug("%s", sql);

    return rc;
}

int SqlInsertSceneListModuleDev(FastConbleSceneListModuleDev *moduleDev)
{
    if (moduleDev == NULL || moduleDev->moduleid == NULL)
    {
        log_error("Error: Invalid input parameters moduleDev[%p] moduleid[%p]",
                  (void*)moduleDev, (void*)moduleDev->moduleid);
        return SQLITE_ERROR;
    }

    char sql[512] = "";
    int rowCount = 0;

    // 查询记录是否存在
    sqlite3_snprintf(1024, sql, "SELECT count(moduleid) FROM fast_conble_scenelist_moduledev "
                    "where moduleid='%s'", moduleDev->moduleid);
    int rc = sqlite3_exec(db_handle, sql, SqlCallbackRowCount, (void *)&rowCount, 0);
    if (rc != SQLITE_OK)
    {
        log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        return rc;
    }
    log_debug("%s", sql);

    if (rowCount == 0)  //不存在记录，则插入语句
    {
        sqlite3_snprintf(512, sql, "INSERT INTO fast_conble_scenelist_moduledev "
                        "(moduleid, sdid, content, did) VALUES ('%s', '%s', '%s', '%s')",
                        moduleDev->moduleid, moduleDev->sdid, moduleDev->content, moduleDev->did);

        // 执行SQL语句
        int rc = sqlite3_exec(db_handle, sql, 0, 0, 0);
        if (rc != SQLITE_OK)
        {
            log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        }
    }
    else
    {
        sqlite3_snprintf(2048, sql, "UPDATE fast_conble_scenelist_moduledev SET "
                                "sdid = '%s', "
                                "content = '%s', "
                                "did = '%s' "
                                "WHERE moduleid = '%s'",
                     moduleDev->sdid, moduleDev->content, moduleDev->did, moduleDev->moduleid);

        // 执行更新操作
        int rc = sqlite3_exec(db_handle, sql, 0, 0, 0);
        if (rc != SQLITE_OK)
        {
            log_error("SQL error: %s", sqlite3_errmsg(db_handle));
        }
    }
    log_debug("%s", sql);
    
    return rc;
}
